﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.DTO
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports DatabaseAccess.DBUtility
Imports External.APICalling
Public Class uMES_bjStandFunctionBusiness
    Implements uMES_bjStandFunctionIBusiness
    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()
    ''' <summary>
    ''' 获取批次当前工序列表
    ''' </summary>
    Function GetCurrentContianerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjStandFunctionIBusiness.GetCurrentContianerInfo
        Dim result As New uMESProcessResult
        Try
            Dim strTogetherFlag As String = ""
            Dim strContainerName As String = ""
            Dim strProductName As String = ""
            Dim commBusiness As uMESCommonBusiness = New uMESCommonBusiness()

            If para.Keys.Contains("containername") = True Then
                If para("containername").ToString().Trim() <> "" Then
                    strContainerName = commBusiness.ReplaceContainerName(para("containername").ToString.Trim())
                    Dim dtTmp As DataTable = GetContainerInfo(strContainerName)
                    If dtTmp.Rows(0).IsNull("TogetherFlag") = False Then
                        strTogetherFlag = dtTmp.Rows(0)("TogetherFlag").ToString().Trim()
                        strProductName = dtTmp.Rows(0)("productname").ToString().Trim()
                    End If
                End If
            End If

            If para.Keys.Contains("containernamelist") = True Then
                If para("containernamelist").ToString().Trim() <> "" Then
                    strTogetherFlag = ""
                End If
            End If


            Dim strSQL As String = "SELECT distinct c.containername,c.qty,c.containertype,c.childcount,c.parentcontainerid,c.containerid,c.togetherflag," & vbCrLf & _
                    "pb.productname,p.productrevision,p.description ProductDesc,c.mfgorderid,mo.mfgordername,e.fullname," & vbCrLf & _
                    "workflowname,workflowrevision,w.workflowid,sb.specname,s.specid,s.specrevision," & vbCrLf & _
                    "f.factoryname,f.description factoryDescc,sr.startreasonname,o.ownername,u.uomname," & vbCrLf & _
                    "decode(ua.attributevalue,1,'待派工',2,'待报工',3,'待检验',4,'已转工') status,ee.fullname CheckTechnique," & vbCrLf & _
                    "case when wcd.workcenterdispatchingname is null then '' else 'dispatch' end workcenterdispatchingname " & vbCrLf & _
                    "FROM container c " & vbCrLf & _
                    "LEFT JOIN product p on p.productid = c.productid" & vbCrLf & _
                    "LEFT JOIN productbase pb on pb.productbaseid = p.productbaseid" & vbCrLf & _
                    "LEFT JOIN currentstatus cs on cs.currentstatusid=c.currentstatusid" & vbCrLf & _
                    "INNER JOIN WorkflowStep wfs ON cs.WorkflowStepid = wfs.WorkflowStepid" & vbCrLf & _
                    "INNER JOIN Workflow w ON w.Workflowid = wfs.Workflowid" & vbCrLf & _
                    "INNER JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid" & vbCrLf & _
                    "LEFT JOIN Spec S ON cs.Specid = S.Specid" & vbCrLf & _
                    "LEFT JOIN Specbase SB ON SB.Specbaseid = S.Specbaseid" & vbCrLf & _
                    "LEFT JOIN factory f on f.factoryid=cs.factoryid" & vbCrLf & _
                    "LEFT JOIN startreason sr on sr.startreasonid=c.startreasonid" & vbCrLf & _
                    "LEFT JOIN owner o on o.ownerid= c.ownerid" & vbCrLf & _
                    "LEFT JOIN uom u on u.uomid= c.uomid" & vbCrLf & _
                    "LEFT JOIN workcenterdispatching wcd on wcd.containerid= c.containerid and wcd.specid=s.specid" & vbCrLf & _
                    "LEFT JOIN employee e ON e.employeeid=wcd.mainmfgemployeeid " & vbCrLf & _
                    "LEFT JOIN Userattribute ua ON ua.parentid=c.containerid  AND ua.userattributename='currSpecStatus'" & vbCrLf & _
                    "LEFT JOIN Userattribute ub ON ub.parentid=c.containerid  AND ub.userattributename='CheckTechnique'" & vbCrLf & _
                    "LEFT JOIN employee ee ON ee.employeename=ub.attributevalue " & vbCrLf & _
                    "LEFT JOIN mfgorder mo on mo.mfgorderid=c.mfgorderid" & vbCrLf & _
                    "WHERE c.containerid<>c.containername and c.parentcontainerid is null "

            If strTogetherFlag <> "" Then
                strSQL += "AND c.togetherflag = '" & strTogetherFlag & "'" & vbCrLf
                strSQL += " AND  LOWER(pb.productname) = '" & strProductName.ToLower & "' " & vbCrLf
            Else
                If para.Keys.Contains("containername") = True Then
                    strSQL += " AND  LOWER(c.containername)= '" & strContainerName.ToLower & "' " & vbCrLf
                End If
            End If

            If para.Keys.Contains("contSearch") = True Then
                If para("contSearch").ToString.Trim() <> "" Then
                    strSQL += " AND  LOWER(c.containername) LIKE '%" & para("contSearch").ToString.ToLower & "%' " & vbCrLf
                End If
            End If

            If para.Keys.Contains("containernamelist") = True Then
                strSQL += " AND  LOWER(c.containername) in (" & para("containernamelist").ToString.ToLower & ")" & vbCrLf
            End If

            If para.Keys.Contains("productname") = True Then
                strSQL += " AND  LOWER(pb.productname) LIKE '%" & para("productname").ToString.ToLower & "%' " & vbCrLf
            End If

            If para.Keys.Contains("factoryname") = True Then
                strSQL += " AND  f.factoryname= '" & para("factoryname").ToString() & "' " & vbCrLf
            End If

            If para.Keys.Contains("productdesc") = True Then
                strSQL += " AND  LOWER(p.description) LIKE '%" & para("productdesc").ToString.ToLower & "%' " & vbCrLf
            End If

            '工艺路线id
            If para.Keys.Contains("workflowid") = True Then
                strSQL += " AND  wfs.workflowid = '" & para("workflowid").ToString & "' " & vbCrLf
            End If
            '工序id
            'If para.Keys.Contains("specid") = True Then
            '    strSQL += " AND  s.specid = '" & para("specid").ToString & "' " & vbCrLf
            'End If

            If para.Keys.Contains("specname") = True Then
                strSQL += " AND  LOWER(sb.specname) LIKE '%" & para("specname").ToString.ToLower & "%' " & vbCrLf
            End If

            If para.Keys.Contains("specno") = True Then
                strSQL += " AND  LOWER(s.specno) = '" & para("specno").ToString.ToLower & "'" & vbCrLf
            End If

            If para.Keys.Contains("status") = True Then
                strSQL += para("status").ToString & vbCrLf
            End If

            strSQL += " Order by c.ContainerName"


            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSQL

            Dim r As uMESProcessResult = dal.RunProcedureForPage(paraPage)
            Dim DT As DataTable = r.DBTable
            DT.Columns.Add("specNameNew")
            For Each row As DataRow In DT.Rows
                row("specNameNew") = CurrentInfo.ReplaceProductNameForSpec(row("specname").ToString().Trim())
            Next
            result.Result = True
            result.DBTable = DT
            result.RowCount = r.RowCount

        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function

    ''' <summary>
    ''' 获取批次数量变更原因
    ''' </summary>
    Function GetQtyAdjustReason() As DataTable Implements uMES_bjStandFunctionIBusiness.GetQtyAdjustReason
        Dim strSQL As String
        strSQL = "select t.qtyadjustreasonname from qtyadjustreason t " & vbCrLf

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

#Region "获取跟踪卡基本信息"
    Function GetContainerInfo(ByVal strContainerName As String) As DataTable
        'Dim strQuery As String = "SELECT c.containername,c.togetherflag FROM container c where c.containername = '" & strContainerName & "'"
        'Dim DT As DataTable = dal.GetContainerInfo(strQuery)
        'Return DT

        Dim strSql As String = "SELECT c.containername,c.togetherflag,pb.productname FROM container c " & vbCrLf & _
                             "LEFT JOIN product p ON p.productid=c.productid " & vbCrLf & _
                             "LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid " & vbCrLf & _
                             "WHERE c.containername='" & strContainerName & "'"
        Dim DT As DataTable = dal.StandardGetDataTable(strSql)
        Return DT
    End Function
#End Region

#Region "检查派工单情况"
    Public Function CheckDispatching(ByVal strConList As String) As DataTable Implements uMES_bjStandFunctionIBusiness.CheckDispatching
        Dim strQuery As String = "select t.qty,nvl( t.reportedqty,0) as reportedqty,t.workcenterdispatchingname  from workcenterdispatching t" & vbCrLf & _
                                 "left join container c on c.containerid=t.containerid where c.containername in (" & strConList & ")"
        Dim DT As DataTable = dal.GetContainerInfo(strQuery)
        Return DT
    End Function
#End Region

#Region "检查转工单情况"
    Public Function CheckSynergic(ByVal strConList As String) As DataTable Implements uMES_bjStandFunctionIBusiness.CheckSynergic
        Dim strQuery As String = "select t.returnspecid from SynergicInfo t left join container c on c.containerid=t.containerid " & vbCrLf & _
                                 "where  c.containername in (" & strConList & ")"
        Dim DT As DataTable = dal.GetContainerInfo(strQuery)
        Return DT
    End Function
#End Region

End Class
